Fixing ORA-06502 in C

A bug appears

A few days ago a friend asked me to help him figure out a bug that was reported. Thanks to the error report he was already able to trace the bug to a specific code fragment, but he was wondering why it failed - the code looked perfectly fine.

The cause

The code was interfacing with an oracle database and was calling a stored procedure. As a stored procedures can't return a value by itself, the usual way to retrieve data is to use an output parameter. The C# code sets the parameter up by declaring it's type, size and direction and the stored procedure is then able to access and update it. After the control returns back to the caller, you can access the parameter and use it's filled value.

The error message was: ORA-06502: PL/SQL: numeric or value error: character string buffer too small exception

In this case the calling code was:

cmd.Parameters.Add("param1", OracleDbType.Varchar2, 512, Direction.Output);

When we took a look at signature of the offending method, we quickly spotted the bug:

public OracleParameter Add(string name, OracleDbType dbType, object val, ParameterDirection dir)

The problem was with the third parameter - the caller thought he was initializing the size of the output parameter, but instead he was supplying the initial value - which isn't used for an output parameter anyway.

Fixing the code was easy by setting the size of the parameter explicitedly and we could have called it a day, but I've seen a bug just like this before and so I wondered how this mistake could have happened and I took a second look.

The root cause

Digging deeper, I found the following overload:

public OracleParameter Add(string name, OracleDbType dbType, int size)

And then it dawned on me! Can you see what happened?

When the caller of the method started typing, he saw the overload where the third parameter is the size of type int and the correct overload is chosen.

It looked something like this:

cmd.Parameters.Add("param1", OracleDbType.Varchar2, 512

But he didn't stop here, he continued on, because he wanted to supply the ParameterDirection as well and the moment he did so, the other overload was chosen, where the third parameter is now the value and not the size!

The caller didn't notice, as an int converts nicely to an object and the signatures match up.

Bad API design

The culprit in this scenario was bad design on oracles behalf when they added overloads that change the semantics of a parameter at a certain position.

If the types of the parameters would have differed sufficently it would only be a nuisance for the developer, as the compiler would have caught the error, but to make matters worse the types used were implicetly convertable from one to the other and therefore the compiler was of no help.

General guidelines for Member Overloading are documented nicely on the msdn and while you may freely ignore those design principles in your own applications (even if they make sense) in a professional public facing API you really should follow them, your customers will thank you for it.

References

Member Overloading on msdn (https://msdn.microsoft.com/en-us/library/ms229029(v=vs.110).aspx)